﻿CREATE PROCEDURE [dbo].[Inventories_InsertFromTempInventories]
AS
	INSERT INTO Inventories(Date, StoreId, ArticleId, Description, Quantity, Cost, Retail, GP)
	SELECT Date, StoreId, repArtShare.Id, Description, Quantity, temp.Cost * ISNULL(repArtShare.SalesShare, 1) Cost, Retail, GP
	FROM TempInventories temp
	LEFT JOIN
	(
		select  distinct a.Id, a.Code, a.UOM, SUM(isnull(s.Sales, 0) / rep.CodeSales) SalesShare
		from Articles a
		Left join 
		(
			select Code, SUM(convert(decimal(18,4),Sales)) CodeSales
			from Articles ar 
			left join ArticleSales s on s.ArticleId = ar.Id
			where ar.Code in (select Code from Articles group by Code having COUNT(*) > 1)
			group by Code		
		) as rep on rep.Code = a.Code
		left join ArticleSales s on a.Id = s.ArticleId
		group by a.Id, a.Code, a.UOM
	) repArtShare on repArtShare.Code = temp.ArticleCode
	WHERE
		NOT EXISTS
			(select * 
			 from Inventories inv
			 where inv.Date = temp.Date and
				   inv.StoreId = temp.StoreId and
				   inv.ArticleId = temp.ArticleId) 
	AND	StoreId <> 0
	AND ArticleId <> 0
	AND ISNULL(temp.Cost, 0) > 0
	group by Date, StoreId, repArtShare.Id, repArtShare.Code, repArtShare.UOM, Description, Quantity, temp.Cost, Retail, GP, repArtShare.SalesShare